1 Imports System.Data.SqlClient
2 Imports System.IO
3
4 Public Class frmCustomer
5 Dim s As String
6 Dim Photoname As String = ""
7 Dim IsImageChanged As Boolean = False
8 Sub Reset()
9 txtCustomerName.Text = ""
10 txtAddress.Text = ""
11 txtRemarks.Text = ""
12 txtCustomerName.Text = ""
13 txtCustomerID.Text = ""
14 txtContactNo.Text = ""
15 txtEmailID.Text = ""
16
17 txtZipCode.Text = ""
18 rbMale.Checked = False
19 rbFemale.Checked = False
20 txtCity.Text = ""
21 txtCustomerName.Focus()
22 btnSave.Enabled = True
23 btnUpdate.Enabled = False
24 btnDelete.Enabled = False
25 Picture.Image = My.Resources.photo
26 auto()
27 cmbState.Text = ""
28 End Sub
29 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
30 Me.Close()
31 frmCustomerRecord2.Reset()
32 End Sub
33 Private Function GenerateID() As String
34 con = New SqlConnection(cs)
35 Dim value As String = "0000"
36 Try
37 ' Fetch the latest ID from the database
38 con.Open()
39 cmd = New SqlCommand("SELECT TOP 1 ID FROM Customer ORDER BY ID DESC", con)
40 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
41 If rdr.HasRows Then
42 rdr.Read()
43 value = rdr.Item("ID")
44 End If
45 rdr.Close()
46 ' Increase the ID by 1
47 value += 1
48 ' Because incrementing a string with an integer removes 0's
49 ' we need to replace them. If necessary.
50 If value <= 9 Then 'Value is between 0 and 10
51 value = "000" & value
52 ElseIf value <= 99 Then 'Value is between 9 and 100
53 value = "00" & value
54 ElseIf value <= 999 Then 'Value is between 999 and 1000
55 value = "0" & value
56 End If
57 Catch ex As Exception
58 ' If an error occurs, check the connection state and close it if necessary.
59 If con.State = ConnectionState.Open Then
60 con.Close()
61 End If
62 value = "0000"
63 End Try
64 Return value
65 End Function
66 Sub auto()
67 Try
68 txtID.Text = GenerateID()
69 txtCustomerID.Text = "C-" + GenerateID()
70 Catch ex As Exception
71 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
72 End Try
73 End Sub
74
75 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
76 If Len(Trim(txtCustomerName.Text)) = 0 Then
77 MessageBox.Show("Please enter Customer name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
78 txtCustomerName.Focus()
79 Exit Sub
80 End If
81 If ((rbMale.Checked = False) And (rbFemale.Checked = False)) Then
82 MessageBox.Show("Please select gender", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
83 Exit Sub
84 End If
85 If Len(Trim(txtAddress.Text)) = 0 Then
86 MessageBox.Show("Please Enter Address", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
87 txtAddress.Focus()
88 Exit Sub
89 End If
90 If Len(Trim(txtCity.Text)) = 0 Then
91 MessageBox.Show("Please Enter City", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
92 txtCity.Focus()
93 Exit Sub
94 End If
95 If Len(Trim(txtContactNo.Text)) = 0 Then
96 MessageBox.Show("Please Enter Contact No.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
97 txtContactNo.Focus()
98 Exit Sub
99 End If
100
101 Try
102 con = New SqlConnection(cs)
103 con.Open()
104 Dim ct As String = "select RTRIM(ContactNo) from Customer where ContactNo=@d1"
105 cmd = New SqlCommand(ct)
106 cmd.Parameters.AddWithValue("@d1", txtContactNo.Text)
107 cmd.Connection = con
108 rdr = cmd.ExecuteReader()
109
110 If rdr.Read() Then
111 MessageBox.Show("Entered contact no. is already registered", "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
112 If (rdr IsNot Nothing) Then
113 rdr.Close()
114 End If
115 Return
116 End If
117 con.Close()
118 If (rbMale.Checked = True) Then
119 s = rbMale.Text
120 End If
121 If (rbFemale.Checked = True) Then
122 s = rbFemale.Text
123 End If
124 con = New SqlConnection(cs)
125 con.Open()
126 Dim cb As String = "insert into Customer(ID, CustomerID, [Name], Gender, Address, City, ContactNo, EmailID,Remarks,State,ZipCode,Photo,CustomerType) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8,@d9,@d10,@d11,@d12,'Regular')"
127 cmd = New SqlCommand(cb)
128 cmd.Parameters.AddWithValue("@d1", txtID.Text)
129 cmd.Parameters.AddWithValue("@d2", txtCustomerID.Text)
130 cmd.Parameters.AddWithValue("@d3", txtCustomerName.Text)
131 cmd.Parameters.AddWithValue("@d4", s)
132 cmd.Parameters.AddWithValue("@d5", txtAddress.Text)
133 cmd.Parameters.AddWithValue("@d6", txtCity.Text)
134 cmd.Parameters.AddWithValue("@d7", txtContactNo.Text)
135 cmd.Parameters.AddWithValue("@d8", txtEmailID.Text)
136 cmd.Parameters.AddWithValue("@d9", txtRemarks.Text)
137 cmd.Parameters.AddWithValue("@d10", cmbState.Text)
138 cmd.Parameters.AddWithValue("@d11", txtZipCode.Text)
139 cmd.Connection = con
140 Dim ms As New MemoryStream()
141 Dim bmpImage As New Bitmap(Picture.Image)
142 bmpImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
143 Dim data As Byte() = ms.GetBuffer()
144 Dim p As New SqlParameter("@d12", SqlDbType.Image)
145 p.Value = data
146 cmd.Parameters.Add(p)
147 cmd.ExecuteNonQuery()
148 LogFunc(lblUser.Text, "added the new Customer having Customer id '" & txtCustomerID.Text & "'")
149 MessageBox.Show("Successfully saved", "Customer Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
150 btnSave.Enabled = False
151 fillState()
152 con.Close()
153 Catch ex As Exception
154 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
155 End Try
156 End Sub
157
158 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
159 Try
160 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
161 DeleteRecord()
162 End If
163 Catch ex As Exception
164 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
165 End Try
166 End Sub
167 Private Sub DeleteRecord()
168 Try
169 Dim RowsAffected As Integer = 0
170 con = New SqlConnection(cs)
171 con.Open()
172 Dim cl As String = "SELECT Customer.ID FROM Customer INNER JOIN InvoiceInfo ON Customer.ID = InvoiceInfo.CustomerID where Customer.ID=@d1"
173 cmd = New SqlCommand(cl)
174 cmd.Connection = con
175 cmd.Parameters.AddWithValue("@d1", txtID.Text)
176 rdr = cmd.ExecuteReader()
177 If rdr.Read Then
178 MessageBox.Show("Unable to delete..Already in use in Billing", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
179 If Not rdr Is Nothing Then
180 rdr.Close()
181 End If
182 Exit Sub
183 End If
184 con.Close()
185 con = New SqlConnection(cs)
186 con.Open()
187 Dim cl1 As String = "SELECT Customer.ID FROM Customer INNER JOIN Quotation ON Customer.ID = Quotation.CustomerID where Customer.ID=@d1"
188 cmd = New SqlCommand(cl1)
189 cmd.Connection = con
190 cmd.Parameters.AddWithValue("@d1", txtID.Text)
191 rdr = cmd.ExecuteReader()
192 If rdr.Read Then
193 MessageBox.Show("Unable to delete..Already in use in Quotation", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
194 If Not rdr Is Nothing Then
195 rdr.Close()
196 End If
197 Exit Sub
198 End If
199 con.Close()
200 con = New SqlConnection(cs)
201 con.Open()
202 Dim cl2 As String = "SELECT Customer.ID FROM Customer INNER JOIN Service ON Customer.ID = Service.CustomerID where Customer.ID=@d1"
203 cmd = New SqlCommand(cl2)
204 cmd.Connection = con
205 cmd.Parameters.AddWithValue("@d1", txtID.Text)
206 rdr = cmd.ExecuteReader()
207 If rdr.Read Then
208 MessageBox.Show("Unable to delete..Already in use in Services", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
209 If Not rdr Is Nothing Then
210 rdr.Close()
211 End If
212 Exit Sub
213 End If
214 con.Close()
215
216 con = New SqlConnection(cs)
217 con.Open()
218 Dim cq As String = "delete from Customer where ID =" & txtID.Text & ""
219 cmd = New SqlCommand(cq)
220 cmd.Connection = con
221 RowsAffected = cmd.ExecuteNonQuery()
222 If RowsAffected > 0 Then
223 LogFunc(lblUser.Text, "deleted the Customer record having Customer id '" & txtCustomerID.Text & "'")
224 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
225 Reset()
226 fillState()
227 Else
228 MessageBox.Show("No record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
229 Reset()
230 If con.State = ConnectionState.Open Then
231 con.Close()
232 End If
233 con.Close()
234 End If
235 Catch ex As Exception
236 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
237 End Try
238 End Sub
239
240 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
241 If Len(Trim(txtCustomerName.Text)) = 0 Then
242 MessageBox.Show("Please enter Customer name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
243 txtCustomerName.Focus()
244 Exit Sub
245 End If
246 If ((rbMale.Checked = False) And (rbFemale.Checked = False)) Then
247 MessageBox.Show("Please select gender", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
248 Exit Sub
249 End If
250 If Len(Trim(txtAddress.Text)) = 0 Then
251 MessageBox.Show("Please Enter Address", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
252 txtAddress.Focus()
253 Exit Sub
254 End If
255 If Len(Trim(txtCity.Text)) = 0 Then
256 MessageBox.Show("Please Enter City", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
257 txtCity.Focus()
258 Exit Sub
259 End If
260 If cmbState.Text = "" Then
261 MessageBox.Show("Please enter state", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
262 cmbState.Focus()
263 Return
264 End If
265 If Len(Trim(txtContactNo.Text)) = 0 Then
266 MessageBox.Show("Please Enter Contact No.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
267 txtContactNo.Focus()
268 Exit Sub
269 End If
270
271 Try
272 If (rbMale.Checked = True) Then
273 s = rbMale.Text
274 End If
275 If (rbFemale.Checked = True) Then
276 s = rbFemale.Text
277 End If
278 con = New SqlConnection(cs)
279 con.Open()
280 Dim cb As String = "update Customer set CustomerID=@d2,[Name]=@d3,Gender=@d4, Address=@d5,City=@d6, ContactNo=@d7, EmailID=@d8,Remarks=@d9,State=@d10,ZipCode=@d11,Photo=@d12,CustomerType='Regular' where ID=@d1"
281 cmd = New SqlCommand(cb)
282
283 cmd.Parameters.AddWithValue("@d2", txtCustomerID.Text)
284 cmd.Parameters.AddWithValue("@d3", txtCustomerName.Text)
285 cmd.Parameters.AddWithValue("@d4", s)
286 cmd.Parameters.AddWithValue("@d5", txtAddress.Text)
287 cmd.Parameters.AddWithValue("@d6", txtCity.Text)
288 cmd.Parameters.AddWithValue("@d7", txtContactNo.Text)
289 cmd.Parameters.AddWithValue("@d8", txtEmailID.Text)
290 cmd.Parameters.AddWithValue("@d9", txtRemarks.Text)
291 cmd.Parameters.AddWithValue("@d10", cmbState.Text)
292 cmd.Parameters.AddWithValue("@d11", txtZipCode.Text)
293 cmd.Connection = con
294 Dim ms As New MemoryStream()
295 Dim bmpImage As New Bitmap(Picture.Image)
296 bmpImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
297 Dim data As Byte() = ms.GetBuffer()
298 Dim p As New SqlParameter("@d12", SqlDbType.Image)
299 p.Value = data
300 cmd.Parameters.Add(p)
301 cmd.Parameters.AddWithValue("@d1", txtID.Text)
302 cmd.ExecuteNonQuery()
303 LogFunc(lblUser.Text, "updated the Customer having Customer id '" & txtCustomerID.Text & "'")
304 MessageBox.Show("Successfully updated", "Customer Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
305 btnUpdate.Enabled = False
306 con.Close()
307 fillState()
308 Catch ex As Exception
309 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
310 End Try
311 End Sub
312
313 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
314 Reset()
315 End Sub
316
317
318 Private Sub BStartCapture_Click(sender As System.Object, e As System.EventArgs) Handles BStartCapture.Click
319 Dim k As New frmCamera
320 k.ShowDialog()
321 If TempFileNames2.Length > 0 Then
322
323 Picture.Image = Image.FromFile(TempFileNames2)
324 Photoname = TempFileNames2
325 IsImageChanged = True
326 End If
327 End Sub
328
329 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
330 Dim frm As New frmCustomerRecord
331 frm.lblSet.Text = "Customer Entry"
332 frm.Getdata()
333 frm.ShowDialog()
334 End Sub
335 Sub fillState()
336 Try
337 con = New SqlConnection(cs)
338 con.Open()
339 adp = New SqlDataAdapter()
340 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(State) FROM Customer order by 1", con)
341 ds = New DataSet("ds")
342 adp.Fill(ds)
343 dtable = ds.Tables(0)
344 cmbState.Items.Clear()
345 For Each drow As DataRow In dtable.Rows
346 cmbState.Items.Add(drow(0).ToString())
347 Next
348 Catch ex As Exception
349 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
350 End Try
351 End Sub
352 Private Sub Browse_Click(sender As System.Object, e As System.EventArgs) Handles Browse.Click
353 Try
354 With OpenFileDialog1
355 .Filter = ("Images |*.png; *.bmp; *.jpg;*.jpeg; *.gif;")
356 .FilterIndex = 4
357 End With
358 'Clear the file name
359 OpenFileDialog1.FileName = ""
360 If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
361 Picture.Image = Image.FromFile(OpenFileDialog1.FileName)
362 End If
363 Catch ex As Exception
364 MsgBox(ex.ToString())
365 End Try
366 End Sub
367
368 Private Sub BRemove_Click(sender As System.Object, e As System.EventArgs) Handles BRemove.Click
369 Picture.Image = My.Resources.photo
370 End Sub
371
372 Private Sub frmCustomer_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
373 fillState()
374 End Sub
375
376 Private Sub cmbState_Format(sender As System.Object, e As System.Windows.Forms.ListControlConvertEventArgs) Handles cmbState.Format
377 If (e.DesiredType Is GetType(String)) Then
378 e.Value = e.Value.ToString.Trim
379 End If
380 End Sub
381 End Class
